﻿Imports MySql.Data.MySqlClient
Imports System.Threading
Imports Microsoft.Office.Interop

Public Class frmview_ctnempty
    Dim mySqlCommand As New MySqlCommand
    Dim mySqlAdaptor As New MySqlDataAdapter
    Dim mySqlReader As MySqlDataReader
    Dim respone As Object
    Dim mysql As MySqlConnection = main_form.mysqlconection
    Public Delegate Sub DelegateSub(ByVal x As Integer)
    Dim pathExcel As String
    Dim diff1 As System.TimeSpan

    Dim date1 As Date
    Dim date2 As Date
    Dim subdate1() As String
    Dim subdate2() As String
    Dim valuedate As String
    Dim dateend As String


    Private Sub frmview_ctnempty_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
        System.Threading.Thread.CurrentThread.CurrentUICulture = System.Threading.Thread.CurrentThread.CurrentCulture
        mysql.Close()

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        mySqlCommand.CommandText = "Select  CTNMAINID,CTNSTRING,CTNAGENT,CTNCONSI,VOYDATEEN,CTNSIZE,CTNVOYN,CTNSHIPNAME,CTNBOOKING,CTNYARD,idborrow,ctnmainid from ctnmain join voyage on ctnmain.CTNVOYN = voyage.VOYAGEID left join borrow on ctnmain.CTNMAINID = borrow.CTNID  where CTNSTAT= '1' order by CTNMAINID ASC;"
        ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand
        Try
            mySqlReader = mySqlCommand.ExecuteReader

            While (mySqlReader.Read())

                subdate1 = Split(mySqlReader("VOYDATEEN"), "-")
                subdate2 = Split(Date.Now.ToString("dd-MM-yyyy"), "-")
                date1 = New System.DateTime(subdate1(2), subdate1(1), subdate1(0), 0, 0, 0)
                date2 = New System.DateTime(subdate2(2), subdate2(1), subdate2(0), 0, 0, 0)
                diff1 = date2.Subtract(date1)
                valuedate = diff1.TotalDays.ToString

                If mySqlReader("idborrow") Is DBNull.Value Then
                    DataGridView1.Rows.Add({mySqlReader("CTNSTRING"), mySqlReader("CTNAGENT"), mySqlReader("CTNSIZE"), mySqlReader("CTNSHIPNAME"), mySqlReader("CTNBOOKING"), mySqlReader("CTNYARD"), mySqlReader("CTNVOYN"), mySqlReader("VOYDATEEN"), valuedate})
                Else

                    DataGridView2.Rows.Add({mySqlReader("CTNSTRING"), mySqlReader("CTNAGENT"), mySqlReader("CTNSIZE"), mySqlReader("CTNSHIPNAME"), mySqlReader("CTNBOOKING"), mySqlReader("CTNYARD"), mySqlReader("CTNVOYN"), mySqlReader("VOYDATEEN"), valuedate})

                End If

            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()
        TextBoxX1.Text = (DataGridView1.RowCount - 1).ToString
        TextBoxX2.Text = (DataGridView2.RowCount - 1).ToString
        TextBoxX3.Text = CStr(DataGridView1.RowCount + DataGridView2.RowCount - 1)

        checkcolor()

    End Sub
    Private Sub ButtonX1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX1.Click
        FolderBrowserDialog1.Description = "Pick Folder to store Excecl files"
        FolderBrowserDialog1.ShowNewFolderButton = True
        FolderBrowserDialog1.SelectedPath = "C:\"


        If FolderBrowserDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
            pathExcel = FolderBrowserDialog1.SelectedPath
            excelReport()


        End If

    End Sub
    Private Sub excelReport()
        Dim count_Row As Integer = 5
        Dim count_Row2 As Integer = 9


        Dim excelapp As New Excel.Application
        Dim excelbooks As Excel.Workbook
        Dim excelsheets As Excel.Worksheet
        excelbooks = excelapp.Workbooks.Add
        excelsheets = CType(excelbooks.Worksheets(1), Excel.Worksheet)
        excelsheets.Rows("3:3").rowheight = 20


        With (excelsheets)
            .Range("A1:Q900").Font.Name = "Angsana New"

            .Range("A2:Q900").Font.Size = 14
            Dim CheckIndex As Integer
            Dim i As Integer
            Dim CheckData As Boolean
            CheckData = False
            'CheckIndex = ListView1.Items.Count
            Dim J As Integer
            For J = 7 To 10
                .Range("A4").Borders(J).Weight = 2 ' xlThin
                .Range("B4").Borders(J).Weight = 2 ' xlThin
                .Range("C4").Borders(J).Weight = 2 ' xlThin
                .Range("D4").Borders(J).Weight = 2 ' xlThin
                .Range("E4").Borders(J).Weight = 2 ' xlThin
                .Range("F4").Borders(J).Weight = 2 ' xlThin


            Next
            With .Range("A2:F2")
                .Merge()


                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "YKP PORT CONTAINER EMPTY"
                .Font.Size = 16
                ''.ColumnWidth = 20
            End With
            With .Range("A3:F3")
                .Merge()
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "Report Container EMPTY"
                .Font.Size = 14
                ''.ColumnWidth = 20
            End With
            With .Range("A4")

                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "Container No."
                .ColumnWidth = 15
            End With
            With .Range("B4")

                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "Agent Line"
                .ColumnWidth = 12
            End With
            With .Range("C4")

                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "SHIPPER"
                .Font.Size = 14

            End With

            With .Range("D4")


                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "Date Recieve"
                .Font.Size = 14
                .ColumnWidth = 20
            End With
            With .Range("E4")

                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "Size"
                .Font.Size = 14
                ''.ColumnWidth = 20
            End With
            With .Range("F4")

                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "VOYN"
                .Font.Size = 14
                ''.ColumnWidth = 20
            End With

            For i = 0 To DataGridView1.Rows.Count - 1


                With .Range("A" + count_Row.ToString)
                    .Value = DataGridView1.Rows(i).Cells(0).Value
                End With

                With .Range("B" + count_Row.ToString)
                    .Value = DataGridView1.Rows(i).Cells(1).Value
                End With
                With .Range("C" + count_Row.ToString)
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                    .Value = DataGridView1.Rows(i).Cells(3).Value
                End With
                With .Range("D" + count_Row.ToString)
                    .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
                    .Value = DataGridView1.Rows(i).Cells(7).Value
                End With
                With .Range("E" + count_Row.ToString)
                    .Value = DataGridView1.Rows(i).Cells(2).Value
                End With
                With .Range("F" + count_Row.ToString)
                    .Value = DataGridView1.Rows(i).Cells(6).Value
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                End With

                count_Row += 1
            Next


            For i = 0 To DataGridView2.Rows.Count - 1

                With .Range("A" + count_Row.ToString)
                    .Value = DataGridView2.Rows(i).Cells(0).Value
                End With

                With .Range("B" + count_Row.ToString)
                    .Value = DataGridView2.Rows(i).Cells(1).Value
                End With
                With .Range("C" + count_Row.ToString)
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                    .Value = DataGridView2.Rows(i).Cells(3).Value
                End With
                With .Range("D" + count_Row.ToString)
                    .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
                    .Value = DataGridView2.Rows(i).Cells(7).Value
                End With
                With .Range("E" + count_Row.ToString)
                    .Value = DataGridView2.Rows(i).Cells(2).Value
                End With
                With .Range("F" + count_Row.ToString)
                    .Value = DataGridView2.Rows(i).Cells(6).Value
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                End With

                count_Row += 1
            Next





            For i = 5 To count_Row
                .Range("B" & i.ToString() & ":B" & i.ToString()).Borders(7).Weight = 2
                .Range("A" & i.ToString() & ":A" & i.ToString()).Borders(7).Weight = 2
                .Range("C" & i.ToString() & ":C" & i.ToString()).Borders(7).Weight = 2
                .Range("D" & i.ToString() & ":D" & i.ToString()).Borders(7).Weight = 2
                .Range("E" & i.ToString() & ":E" & i.ToString()).Borders(7).Weight = 2
                .Range("F" & i.ToString() & ":F" & i.ToString()).Borders(7).Weight = 2
                .Range("G" & i.ToString() & ":G" & i.ToString()).Borders(7).Weight = 2


            Next
            count_Row += 1
            With .Range("A" + count_Row.ToString + ":F" + count_Row.ToString)
                .Borders(8).Weight = 2
            End With

        End With
        Try
            excelbooks.SaveAs(pathExcel.ToString + "\" + "ReportContainerEmpty" + Date.Now.Day.ToString + "-" + Date.Now.Month.ToString + "-" + Date.Now.Year.ToString + ".xlsx")


            MsgBox("Report Complete", MsgBoxStyle.Information, "Complete Report")
            excelsheets = Nothing
            excelbooks.Close()


            excelapp.Quit()

            excelbooks = Nothing


            excelapp = Nothing
        Catch ex As Exception
            MsgBox("NOT SAVED")
        End Try


    End Sub
    Private Sub showResult(ByVal Num As Integer)
        If Label10.InvokeRequired Then
            Dim dlg As New DelegateSub(AddressOf showResult)
            Me.Invoke(dlg, Num)

        Else
            CircularProgress1.IsRunning = False

        End If
    End Sub
    Public Sub searchctn()
        mysql.Close()

        If ComboBox1.Text = "ALL" Then
            mySqlCommand.CommandText = "Select   CTNMAINID,CTNSTRING,CTNAGENT,CTNCONSI,VOYDATEEN,CTNSIZE,CTNVOYN,CTNSHIPNAME,CTNBOOKING,CTNYARD,idborrow,ctnmainid from ctnmain    join voyage on ctnmain.CTNVOYN = voyage.VOYAGEID  left join borrow on ctnmain.CTNMAINID = borrow.CTNID     where CTNSTAT= '1' and(  CTNAGENT like '%" & TextBoxItem2.Text & "%' or CTNVOYN like '%" & TextBoxItem2.Text & "%'  or CTNSTRING like '%" & TextBoxItem2.Text & "%' )  order by CTNMAINID ASC;"

        ElseIf ComboBox1.Text = "20'GP" Then
            mySqlCommand.CommandText = "Select   CTNMAINID,CTNSTRING,CTNAGENT,CTNCONSI,VOYDATEEN,CTNSIZE,CTNVOYN,CTNSHIPNAME,CTNBOOKING,CTNYARD,idborrow,ctnmainid from ctnmain   join voyage on ctnmain.CTNVOYN = voyage.VOYAGEID  left join borrow on ctnmain.CTNMAINID = borrow.CTNID    where CTNSTAT= '1' and CTNSIZE ='20\'GP' and(  CTNAGENT like '%" & TextBoxItem2.Text & "%' or CTNVOYN like '%" & TextBoxItem2.Text & "%'  or CTNSTRING like '%" & TextBoxItem2.Text & "%' )  order by CTNMAINID ASC;"

        ElseIf ComboBox1.Text = "40'HC" Then
            mySqlCommand.CommandText = "Select   CTNMAINID,CTNSTRING,CTNAGENT,CTNCONSI,VOYDATEEN,CTNSIZE,CTNVOYN,CTNSHIPNAME,CTNBOOKING,CTNYARD,idborrow,ctnmainid from ctnmain  join voyage on ctnmain.CTNVOYN = voyage.VOYAGEID  left join borrow on ctnmain.CTNMAINID = borrow.CTNID    where CTNSTAT= '1' and CTNSIZE ='40\'HC' and (  CTNAGENT like '%" & TextBoxItem2.Text & "%' or CTNVOYN like '%" & TextBoxItem2.Text & "%'  or CTNSTRING like '%" & TextBoxItem2.Text & "%' )  order by CTNMAINID ASC;"

        End If

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

         ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand
        Try
            mySqlReader = mySqlCommand.ExecuteReader

            DataGridView1.Rows.Clear()
            DataGridView2.Rows.Clear()

            While (mySqlReader.Read())
            

                    subdate1 = Split(mySqlReader("VOYDATEEN"), "-")
                    subdate2 = Split(Date.Now.ToString("dd-MM-yyyy"), "-")
                    date1 = New System.DateTime(subdate1(2), subdate1(1), subdate1(0), 0, 0, 0)
                    date2 = New System.DateTime(subdate2(2), subdate2(1), subdate2(0), 0, 0, 0)
                    diff1 = date2.Subtract(date1)
                    valuedate = diff1.TotalDays.ToString
             

                If mySqlReader("idborrow") Is DBNull.Value Then
                    DataGridView1.Rows.Add({mySqlReader("CTNSTRING"), mySqlReader("CTNAGENT"), mySqlReader("CTNSIZE"), mySqlReader("CTNSHIPNAME"), mySqlReader("CTNBOOKING"), mySqlReader("CTNYARD"), mySqlReader("CTNVOYN"), mySqlReader("VOYDATEEN"), valuedate})
                Else

                    DataGridView2.Rows.Add({mySqlReader("CTNSTRING"), mySqlReader("CTNAGENT"), mySqlReader("CTNSIZE"), mySqlReader("CTNSHIPNAME"), mySqlReader("CTNBOOKING"), mySqlReader("CTNYARD"), mySqlReader("CTNVOYN"), mySqlReader("VOYDATEEN"), valuedate})

                End If

            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()
        checkcolor()
        TextBoxX1.Text = (DataGridView1.RowCount - 1).ToString
        TextBoxX2.Text = (DataGridView2.RowCount - 1).ToString
        TextBoxX3.Text = CStr(DataGridView1.RowCount + DataGridView2.RowCount - 2)
        'txt_count.Text = ListView1.Items.Count
    End Sub
    Private Sub TextBoxItem2_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles TextBoxItem2.KeyDown
        If e.KeyCode = Keys.Enter Then
            searchctn()
        End If
    End Sub

    Private Sub ButtonX2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX2.Click
        searchctn()
    End Sub
    Public Sub checkcolor()
        Try
            'For counter1 = 0 To ListView1.Items.Count - 1

            '    If CDbl(ListView1.Items(counter1).SubItems(9).Text) > 10 And CDbl(ListView1.Items(counter1).SubItems(9).Text) <= 15 Then
            '        ListView1.Items(counter1).UseItemStyleForSubItems = False
            '        ListView1.Items(counter1).SubItems(9).BackColor = Color.LightYellow
            '    ElseIf CDbl(ListView1.Items(counter1).SubItems(9).Text) > 15 Then
            '        ListView1.Items(counter1).UseItemStyleForSubItems = False
            '        ListView1.Items(counter1).SubItems(9).BackColor = Color.Red
            '    End If
            'Next



            For i As Integer = 0 To DataGridView1.Rows.Count - 1

                If DataGridView1.Rows(i).Cells(8).Value > 10 And DataGridView1.Rows(i).Cells(8).Value <= 15 Then

                    'DGV1.Rows(i).Cells(3).Style.ForeColor = Color.Red

                    'DGV1.DefaultCellStyle.SelectionForeColor = Color.Red

                    DataGridView1.Rows(i).Cells(8).Style.BackColor = Color.LightYellow
                ElseIf DataGridView1.Rows(i).Cells(8).Value > 15 Then

                    DataGridView1.Rows(i).Cells(8).Style.BackColor = Color.Red

                End If


            Next


            For i As Integer = 0 To DataGridView2.Rows.Count - 1

                If DataGridView2.Rows(i).Cells(8).Value > 10 And DataGridView2.Rows(i).Cells(8).Value <= 15 Then

                    'DGV1.Rows(i).Cells(3).Style.ForeColor = Color.Red

                    'DGV1.DefaultCellStyle.SelectionForeColor = Color.Red

                    DataGridView2.Rows(i).Cells(8).Style.BackColor = Color.LightYellow
                ElseIf DataGridView2.Rows(i).Cells(8).Value > 15 Then

                    DataGridView2.Rows(i).Cells(8).Style.BackColor = Color.Red

                End If


            Next
        Catch ex As Exception

        End Try


    End Sub

    Private Sub DataGridView1_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles DataGridView1.RowPostPaint
        Dim dg As DataGridView = DirectCast(sender, DataGridView)
        ' Current row record
        Dim rowNumber As String = (e.RowIndex + 1).ToString()

        ' Format row based on number of records displayed by using leading zeros
        While rowNumber.Length < dg.RowCount.ToString().Length
            rowNumber = "0" & rowNumber
        End While

        ' Position text
        Dim size As SizeF = e.Graphics.MeasureString(rowNumber, Me.Font)
        If dg.RowHeadersWidth < CInt(size.Width + 20) Then
            dg.RowHeadersWidth = CInt(size.Width + 20)
        End If

        ' Use default system text brush
        Dim b As Brush = SystemBrushes.ControlText

        ' Draw row number
        e.Graphics.DrawString(rowNumber, dg.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
    End Sub

    Private Sub DataGridView2_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles DataGridView2.RowPostPaint
        Dim dg As DataGridView = DirectCast(sender, DataGridView)
        ' Current row record
        Dim rowNumber As String = (e.RowIndex + 1).ToString()

        ' Format row based on number of records displayed by using leading zeros
        While rowNumber.Length < dg.RowCount.ToString().Length
            rowNumber = "0" & rowNumber
        End While

        ' Position text
        Dim size As SizeF = e.Graphics.MeasureString(rowNumber, Me.Font)
        If dg.RowHeadersWidth < CInt(size.Width + 20) Then
            dg.RowHeadersWidth = CInt(size.Width + 20)
        End If

        ' Use default system text brush
        Dim b As Brush = SystemBrushes.ControlText

        ' Draw row number
        e.Graphics.DrawString(rowNumber, dg.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
    End Sub
End Class